--
-- Description: Script to split the days to get events for in the Log table.
-- The range will be per file since we process events per file.
-- 2 days will be processed at a time. For this we will need to insert
-- the range start date and range end dates into the Logs table.
-- Date created: 30 Apr 2008
--

set ANSI_NULLS on
set QUOTED_IDENTIFIER on
go

--
-- drop the stored procedure if it exists and recreate it.
--
if exists
(
	select * 
	from sys.objects
	where object_id = object_id(N'[processLogs_sp]')
	and type in (N'P', N'PC')
)
begin
    drop procedure [dbo].[processLogs_sp]
end
go

create procedure [dbo].[processLogs_sp]
(
	@FileID int
	, @MaxFeedDate smalldatetime
)
as
begin
	--
	-- declare local variables.
	--
	declare @MaxRangeEndDate smalldatetime
			, @RangeStartDate smalldatetime
			, @RangeEndDate smalldatetime

	--
	-- get the max range end date from the Logs table
	-- we want to take out the time factor from the date time, so
	-- we have to convert it to a varchar using the convert function
	-- but then we again cast it back to a smalldatetime.
	--
	select @MaxRangeEndDate = isnull(
		max(RangeEndDate)
		, cast(convert(varchar, dateadd(dd, -90, getdate()), 101) as smalldatetime)
		)
	from [dbo].[Logs]
	where FileID = @FileID

	set @RangeStartDate = dateadd(dd, 1, @MaxRangeEndDate)

	while (@RangeStartDate <= @MaxFeedDate)
	begin
		if (@RangeStartDate = @MaxFeedDate)
		begin
			insert into [dbo].[Logs]
			(
				[FileID]
				, [RangeStartDate]
				, [RangeEndDate]
			)
			values
			(
				@FileID
				, @RangeStartDate
				, @RangeStartDate
			)
			break
		end

		set @RangeEndDate = dateadd(dd, 1, @RangeStartDate)

		insert into [dbo].[Logs]
		(
			[FileID]
			, [RangeStartDate]
			, [RangeEndDate]
		)
		values
		(
			@FileID
			, @RangeStartDate
			, @RangeEndDate
		)

		set @RangeStartDate = dateadd(dd, 1, @RangeEndDate)
	end

	select [LogID], [FileID], [RangeStartDate], [RangeEndDate]
	from [dbo].[Logs]
	where [ProcessingEndDate] is null
end
go

set ansi_nulls off
go
set quoted_identifier off
go
